package org.oexam.ostudy.db;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.TreeSet;

import javax.naming.NamingException;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;
import org.oexam.ostudy.facade.struct.TestNameAndId;
import org.oexam.ostudy.facade.struct.TestSetName;
import org.oexam.ostudy.misc.GeneralConstants;
import org.oexam.ostudy.struct.TestSets;
import org.oexam.ostudy.struct.Tests;

public class TestSetsAccess {

	public void insertTestSet(TestSets testSets) {

		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		session.save(testSets);

		session.getTransaction().commit();
		session.close();

	}

	public void insertTestToTestSet(Tests test) {

		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		session.save(test);

		session.getTransaction().commit();
		session.close();

	}

	public int getLastTestSetID() throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "TestSets");

		TreeSet<Integer> allIds = new TreeSet<Integer>();
		while (rs.next()) {
			allIds.add(Integer.parseInt((rs.getString("testSet_id"))));
		}
		rs.close();
		if (!allIds.isEmpty()) {
			return allIds.last();
		} else {
			return 0;
		}
	}

	public Tests getTestDetailsUsingID(int testID) throws SQLException,
			NamingException {

		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		Tests testDetails = (Tests) session.get(Tests.class, testID);

		session.close();

		return testDetails;

	}

	public int getLastTestID() throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "Tests");

		TreeSet<Integer> allIds = new TreeSet<Integer>();
		while (rs.next()) {
			allIds.add(Integer.parseInt((rs.getString("test_id"))));
		}
		rs.close();
		if (!allIds.isEmpty()) {
			return allIds.last();
		} else {
			return 0;
		}
	}

	public boolean checkIfTestSetNameExists(String name) throws SQLException,
			NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "TestSets");

		TreeSet<String> allnames = new TreeSet<String>();
		while (rs.next()) {
			allnames.add(((rs.getString("testSet_name"))));
		}
		rs.close();

		if (allnames.contains(name)) {
			return true;
		} else {
			return false;
		}
	}

	public boolean checkIfTestNameExists(String testname) throws SQLException,
			NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "Tests");

		TreeSet<String> allnames = new TreeSet<String>();
		while (rs.next()) {
			allnames.add(((rs.getString("test_name"))));
		}
		rs.close();

		if (allnames.contains(testname)) {
			return true;
		} else {
			return false;
		}
	}

	public List<TestSetName> getAllTestSetNamesAndIDs() throws SQLException,
			NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "TestSets");

		List<TestSetName> allNamesAndIDs = new ArrayList<TestSetName>();

		while (rs.next()) {
			TestSetName testSetName = new TestSetName();
			testSetName.setTestSetID(Integer.parseInt(rs
					.getString("testSet_id")));
			testSetName.setTestSetName(((rs.getString("testSet_name"))));
			allNamesAndIDs.add(testSetName);
		}
		rs.close();

		return allNamesAndIDs;
	}

	public List<TestNameAndId> getAllTestsNamesAndIDs() throws SQLException,
			NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "Tests");

		List<TestNameAndId> allNamesAndIDs = new ArrayList<TestNameAndId>();

		while (rs.next()) {
			TestNameAndId testName = new TestNameAndId();
			testName.setId(Integer.parseInt(rs.getString("test_id")));
			testName.setTestName(((rs.getString("test_name"))));
			allNamesAndIDs.add(testName);
		}
		rs.close();

		return allNamesAndIDs;
	}

	public List<TestNameAndId> getAllTestsNamesAndIDsUsingTestSetID(
			int testSetId) throws SQLException, NamingException {
		DBConnection dbConnection = new DBConnection();

		ResultSet rs = dbConnection
				.executeQuery(GeneralConstants.select_all_sql + "Tests");

		List<TestNameAndId> allNamesAndIDs = new ArrayList<TestNameAndId>();

		while (rs.next()) {
			TestNameAndId testName = new TestNameAndId();
			testName.setId(Integer.parseInt(rs.getString("test_id")));
			testName.setTestName(((rs.getString("test_name"))));
			if (rs.getInt("test_set") == testSetId) {
				allNamesAndIDs.add(testName);
			}
		}
		rs.close();

		return allNamesAndIDs;
	}

	public TestSets getTestSetDetailsUsingID(int tsID) {
		SessionFactory sf = new Configuration().configure()
				.buildSessionFactory();

		Session session = sf.openSession();

		session.beginTransaction();

		TestSets testSetDetails = (TestSets) session.get(TestSets.class, tsID);

		session.close();

		return testSetDetails;
	}

}
